import json

from apps.settingApi.api.timerTask.apiList.logsAdd import add
from apps.settingApi.api.timerTask.apiList.u9capiUtils import u9cApiList
from apps.settingApi.api.timerTask.apiList.wmsapiUtils import wmsApiList
from comm.dbConnect import dbListJon, dbJonOne, dbEdit, get_yun_session
from comm.openDbConfig import openDbBase


# 送检入库----暂时无用
def wmsapiTiMfRk(db,taskObj):
    print('u9capi采购送检-标准收货')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token,err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
    sql_select_bin = """
select a.bil_no as tz_no from mf_rk a WITH(NOLOCK)
left join mf_rk_z b WITH(NOLOCK)  on a.rk_no=b.rk_no 
where isnull(b.pull_id,'')!='T' 
group by a.bil_no
    """

    sql_select_mf = """
select a.ru_no,a.*,b.* from mf_rk a WITH(NOLOCK)
left join mf_rktz b WITH(NOLOCK) on a.bil_no=b.tz_no 
left join mf_rktz_z c WITH(NOLOCK)  on b.tz_no=c.tz_no
where a.bil_no=:tz_no
    """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_rk a WITH(NOLOCK) 
left join tf_rktz_z b WITH(NOLOCK)  on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_rktz_z c WITH(NOLOCK)  on c.tz_no=b.tz_no and c.itm=b.itm where
where a.bil_no=:tz_no
group by c.u9cid,a.wh,a.bat_no,a.prd_no
"""
    # 更新推送时间
    sql_u = "update mf_rk_z set pull_id='T' where rk_no=:rk_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询进货入库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"tz_no":itm_bin['tz_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询进货入库表头' + sql_select + err
            add(db, logData)
            return

        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新进货入库' + data["ru_no"] + '推送标识异常;' + err
            add(db, logData)
            continue


        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = {}

        logData['order'] = data["ru_no"]

        logData['data'] = json.dumps(addData, ensure_ascii=False)

        add(db, logData)
    return

# 领料出库推送至u9c---暂时无用
def wmsapiPullU9cMlMfCk(db,taskObj):
    print('wms领料出库')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token,err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
    sql_select_bin = """
select a.bil_no as tz_no from mf_ck a WITH(NOLOCK)
left join mf_ck_z b WITH(NOLOCK) on a.ck_no=b.ck_no 
where isnull(b.pull_id,'')!='T'
group by a.bil_no
    """

    sql_select_mf = """
select a.ck_no,a.*,b.* from mf_ck a WITH(NOLOCK)
left join mf_cktz b WITH(NOLOCK) on a.bil_no=b.tz_no 
left join mf_cktz_z c WITH(NOLOCK) on b.tz_no=c.tz_no
where a.bil_no=:tz_no
    """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_ck a WITH(NOLOCK)
left join tf_cktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_cktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.bil_no=:tz_no
group by c.u9cid,a.wh,a.bat_no,a.prd_no
"""
    # 更新推送时间
    sql_u = "update mf_ck_z set pull_id='T' where ck_no=:ck_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询出库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"tz_no":itm_bin['tz_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询出库入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = {}
        logData['order'] = data["ck_no"]

        logData['data'] = json.dumps(addData, ensure_ascii=False)

        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库库' + data["ck_no"] + err
            add(db, logData)
            continue

        add(db, logData)
    return

# 材料出库
def wmsapiPullU9cClMfCk(db, taskObj):
    print('wms材料出库')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
#     sql_select_bin = """
# select c.bil_no as tz_no from mf_ck a
# left join mf_ck_z b on a.ck_no=b.ck_no
# left join tf_ck c on a.ck_no=c.ck_no
# where isnull(b.pull_id,'')!='T'
# group by c.bil_no
#     """
    sql_select_bin = """
select a.ck_no from mf_ck a WITH(NOLOCK)
left join mf_ck_z b WITH(NOLOCK) on a.ck_no=b.ck_no 
left join tf_ck c WITH(NOLOCK) on a.ck_no=c.ck_no
where a.type_id='05' and isnull(b.pull_id,'')!='T'
    """
#     sql_select_mf = """
# select a.ck_no,a.itm,CONVERT(VARCHAR(100), a.ck_no, 23) as ck_no from tf_ck a
# left join mf_ck a1 on a.ck_no=b.ck_no
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
#     """

    sql_select_mf = """
select a.ck_no,CONVERT(VARCHAR(100), a.ck_dd, 23) as ck_dd,a.dep,a.rem from mf_ck a WITH(NOLOCK)
where a.ck_no=:ck_no
    """
#     # 查询需要推送的数据
#     sql_select = """
# select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_ck a
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
# group by c.u9cid,a.wh,a.bat_no,a.prd_no
# """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,cast(isnull(a.qty,0) as float)as qty ,a.bat_no,a.prd_no from tf_ck a WITH(NOLOCK)
left join tf_cktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_cktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.ck_no=:ck_no
"""

    # 更新推送时间
    sql_u = "update mf_ck_z set pull_id='T' where ck_no=:ck_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询出库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"ck_no": itm_bin['ck_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询出库入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['MaterialDeliveryDocType'] = {}
        addData[0]['MaterialDeliveryDocType']['Code'] = 1
        addData[0]['IssueType'] = 0
        addData[0]['Memo'] = data['rem']
        addData[0]['IssueDepartment'] = {}
        # addData[0]['IssueDepartment']['Code'] = data['dep']
        if data['dep'] != '00000000':
            addData[0]['IssueDepartment']['Code'] = data['dep']
        addData[0]['BusinessDate'] = data['ck_dd']
        addData[0]['OtherID'] = data['ck_no']
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['SourceDocType'] = 0
            itm_data['IssueApplyDocLine'] = {
                "ID":itm_1['u9cid']
            }
            itm_data['Wh'] = {
                "Code":itm_1['wh']
            }
            itm_data['IssuedQty'] = itm_1['qty']

            itm_data['Lot'] = {
                "ID":0,
                "Code":itm_1['bat_no']
            }
            itm_data['Item'] = {
                "Code":itm_1['prd_no']
            }
            tf_data_list.append(itm_data)
        addData[0]['MaterialDeliveryDocLines'] = tf_data_list
        logData['order'] = data["ck_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)

        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库标识失败' + data["ck_no"] + err
            add(db, logData)
            continue

        # 调用u9capi生成材料出库单
        u9c_,err = u9cApiList.clCk(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '生成材料出库推推送失败' + data["ck_no"] + err
            add(db, logData)
            continue

        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return
# 材料入库
def wmsapiPullU9cClMfRk(db, taskObj):
    print('wms材料入库')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
#     sql_select_bin = """
# select c.bil_no as tz_no from mf_ck a
# left join mf_ck_z b on a.ck_no=b.ck_no
# left join tf_ck c on a.ck_no=c.ck_no
# where isnull(b.pull_id,'')!='T'
# group by c.bil_no
#     """
    sql_select_bin = """
select a.rk_no from mf_rk a WITH(NOLOCK)
left join mf_rk_z b WITH(NOLOCK) on a.rk_no=b.rk_no 
left join tf_rk c WITH(NOLOCK) on a.rk_no=c.rk_no
where a.type_id='06' and isnull(b.pull_id,'')!='T'
    """
#     sql_select_mf = """
# select a.ck_no,a.itm,CONVERT(VARCHAR(100), a.ck_no, 23) as ck_no from tf_ck a
# left join mf_ck a1 on a.ck_no=b.ck_no
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
#     """

    sql_select_mf = """
select a.rk_no,CONVERT(VARCHAR(100), a.rk_dd, 23) as rk_dd,a.dep,a.rem from mf_rk a WITH(NOLOCK)
where a.rk_no=:rk_no
    """
#     # 查询需要推送的数据
#     sql_select = """
# select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_ck a
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
# group by c.u9cid,a.wh,a.bat_no,a.prd_no
# """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,d.u9cid as wh_id,a.wh,cast(isnull(a.qty,0) as float)as qty ,a.bat_no,a.prd_no from tf_rk a WITH(NOLOCK)
left join tf_rktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_rktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
left join my_wh_z d  WITH(NOLOCK) on a.wh=d.wh
where a.rk_no=:rk_no
"""

    # 更新推送时间
    sql_u = "update mf_rk_z set pull_id='T' where rk_no=:rk_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"rk_no": itm_bin['rk_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询入库入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['MaterialDeliveryDocType'] = {}
        addData[0]['MaterialDeliveryDocType']['Code'] = 1
        addData[0]['IssueType'] = 1
        addData[0]['Memo'] = data['rem']
        addData[0]['IssueDepartment'] = {}
        # addData[0]['IssueDepartment']['Code'] = data['dep']
        if data['dep'] !='00000000':
            addData[0]['IssueDepartment']['Code'] = data['dep']
        addData[0]['BusinessDate'] = data['rk_dd']
        addData[0]['OtherID'] = data['rk_no']
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['SourceDocType'] = 0
            itm_data['IssueApplyDocLine'] = {
                "ID":itm_1['u9cid']
            }
            itm_data['Wh'] = {
                "Code":itm_1['wh'],
                "ID":itm_1['wh_id']
            }
            itm_data['IssuedQty'] = itm_1['qty']

            itm_data['Lot'] = {
                "ID":0,
                "Code":itm_1['bat_no']
            }
            itm_data['Item'] = {
                "Code":itm_1['prd_no']
            }
            tf_data_list.append(itm_data)
        addData[0]['MaterialDeliveryDocLines'] = tf_data_list
        logData['order'] = data["rk_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)

        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库库' + data["ck_no"] + err
            add(db, logData)
            continue

        # 调用u9capi生成材料出库单
        u9c_,err = u9cApiList.clRk(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '生成材料入库推推送失败' + data["rk_no"] + err
            add(db, logData)
            continue

        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# 审核杂收单
def wmsapiPullU9cZsSh(db, taskObj):
    print('审核u9c杂收单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.oth_bil_no as code from mf_rktz a WITH(NOLOCK)
left join mf_rktz_z b WITH(NOLOCK) on a.tz_no=b.tz_no
left join (select sum(isnull(qty,0)) as qty,sum(isnull(qty_rk,0)) as qty_rk,oth_bil_no from 
tf_rktz WITH(NOLOCK) group by oth_bil_no) c on a.oth_bil_no=c.oth_bil_no
where 
isnull(c.oth_bil_no,'')!='' and  c.qty_rk>=c.qty
and type_id=11 and isnull(b.pull_id,'')!='T'
group by a.oth_bil_no
    """
    # 更新推送时间
    sql_u = "update a set a.pull_id='T' from mf_rktz_z a,mf_rktz b where a.tz_no=b.tz_no and b.oth_bil_no=:code"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库通知单' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:
        logData['order'] = itm_bin["code"]
        logData['data'] = json.dumps(itm_bin, ensure_ascii=False)
        # 调用u9capi审核杂收单
        u9c_,err = u9cApiList.zsSh(itm_bin['code'])
        if err != None:
            logData['status'] = False
            logData['rem'] = '审核u9c杂收单失败' + itm_bin["code"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新入库通知推送标识' + itm_bin["code"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return
# 审核杂发单
def wmsapiPullU9cZfSh(db, taskObj):
    print('审核u9c杂发单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.oth_bil_no as code from mf_cktz a WITH(NOLOCK)
left join mf_cktz_z b WITH(NOLOCK) on a.tz_no=b.tz_no
left join (select sum(isnull(qty,0)) as qty,sum(isnull(qty_ck,0) + isnull(qty_rtn,0)) as qty_ck,oth_bil_no 
from tf_cktz WITH(NOLOCK) group by oth_bil_no) c on a.oth_bil_no=c.oth_bil_no
where isnull(c.oth_bil_no,'')!='' and  c.qty_ck>=c.qty
and type_id in('16','12') and isnull(b.pull_id,'')!='T'
group by a.oth_bil_no
    """
    # 更新推送时间
    sql_u = "update a set a.pull_id='T' from mf_cktz_z a,mf_cktz b where a.tz_no=b.tz_no and b.oth_bil_no=:code"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库通知单' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:
        logData['order'] = itm_bin["code"]
        logData['data'] = json.dumps(itm_bin, ensure_ascii=False)
        # 调用u9capi审核杂收单
        u9c_,err = u9cApiList.zfSh(itm_bin['code'])
        if err != None:
            logData['status'] = False
            logData['rem'] = '审核u9c杂发单失败' + itm_bin["code"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库通知推送标识' + itm_bin["code"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# wms推送杂收单
def wmsapiPullU9cZsShMfRk(db, taskObj):
    print('wms推送杂收单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.rk_no,a.rk_no as code from mf_rk a WITH(NOLOCK)
left join mf_rk_z b WITH(NOLOCK) on a.rk_no=b.rk_no
where type_id=11 and isnull(ext_sys_flag,'')='' and isnull(b.pull_id,'')!='T'
    """


    sql_select_mf = """
select a.rk_no,CONVERT(VARCHAR(100), a.rk_dd, 23) as rk_dd,a.dep,a.rem from mf_rk a WITH(NOLOCK)
where a.rk_no=:rk_no
    """

    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,cast(isnull(a.qty,0) as float)as qty ,a.bat_no,a.prd_no from tf_rk a WITH(NOLOCK)
left join tf_rktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_rktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.rk_no=:rk_no
"""

    # 更新推送时间
    sql_u = "update mf_rk_z set pull_id='T' where rk_no=:rk_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"rk_no": itm_bin['rk_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['OtherID'] = itm_bin['code']
        addData[0]['BusinessDate'] = data['rk_dd']
        addData[0]['BenefitOrgCode'] = u9cApiList.orgCode
        addData[0]['Memo'] = data['rem']
        addData[0]['DocStatus'] = 2
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['ItemInfo'] = {
                "ItemCode":itm_1['prd_no']
            }
            itm_data['StoreUOMQty'] = itm_1['qty']
            itm_data['CostPrice'] = 0.01
            itm_data['CostUOMQty'] = 0.01
            itm_data['WhCode'] = itm_1['wh']
            itm_data['LotCode'] = itm_1['bat_no']
            tf_data_list.append(itm_data)
        addData[0]['MiscRcvLines'] = tf_data_list
        logData['order'] = data["rk_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 调用u9capi生成材料出库单
        u9c_,err = u9cApiList.addZsSh(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '入库单推送失败' + data["rk_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新入库库' + data["rk_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# wms推送杂发单
def wmsapiPullU9cZfShMfCk(db, taskObj):
    print('wms推送杂发单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.ck_no,a.ck_no as code from mf_ck a WITH(NOLOCK)
left join mf_ck_z b WITH(NOLOCK) on a.ck_no=b.ck_no
where type_id=16 and isnull(ext_sys_flag,'')='' and isnull(b.pull_id,'')!='T'
    """


    sql_select_mf = """
select a.ck_no,CONVERT(VARCHAR(100), a.ck_dd, 23) as ck_dd,a.dep,a.rem from mf_ck a WITH(NOLOCK)
where a.ck_no=:ck_no
    """

    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,cast(isnull(a.qty,0) as float)as qty ,a.bat_no,a.prd_no from tf_ck a WITH(NOLOCK)
left join tf_cktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_cktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.ck_no=:ck_no
"""

    # 更新推送时间
    sql_u = "update mf_ck_z set pull_id='T' where ck_no=:ck_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询出库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"ck_no": itm_bin['ck_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询出库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['OtherID'] = itm_bin['code']
        addData[0]['BusinessDate'] = data['ck_dd']
        addData[0]['DocTypeCode'] = 'MiscShip001'
        addData[0]['BenefitOrgCode'] = u9cApiList.orgCode
        addData[0]['Memo'] = data['rem']
        addData[0]['DocStatus'] = 2
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['ItemInfo'] = {
                "ItemCode":itm_1['prd_no']
            }
            itm_data['StoreUOMQty'] = itm_1['qty']
            itm_data['CostPrice'] = 0.01
            itm_data['CostUOMQty'] = 0.01
            itm_data['WhCode'] = itm_1['wh']
            itm_data['LotCode'] = itm_1['bat_no']
            tf_data_list.append(itm_data)
        addData[0]['MiscShipLines'] = tf_data_list
        logData['order'] = data["ck_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 调用u9capi生成材料出库单
        u9c_,err = u9cApiList.addZfSh(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '出库单推送失败' + data["ck_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        print('更新单据',sql_u)
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库库' + data["ck_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return


# 进货退回
def wmsapiPullU9cPoMfCk(db, taskObj):
    print('wms进货退回')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    # userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
#     sql_select_bin = """
# select c.bil_no as tz_no from mf_ck a
# left join mf_ck_z b on a.ck_no=b.ck_no
# left join tf_ck c on a.ck_no=c.ck_no
# where isnull(b.pull_id,'')!='T'
# group by c.bil_no
#     """
    sql_select_bin = """
select a.ck_no from mf_ck a WITH(NOLOCK)
left join mf_ck_z b WITH(NOLOCK) on a.ck_no=b.ck_no 
where isnull(b.pull_id,'')!='T' and a.type_id='02'
    """
#     sql_select_mf = """
# select a.ck_no,a.itm,CONVERT(VARCHAR(100), a.ck_no, 23) as ck_no from tf_ck a
# left join mf_ck a1 on a.ck_no=b.ck_no
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
#     """

    sql_select_mf = """
select a.ck_no,CONVERT(VARCHAR(100), a.ck_dd, 23) as ck_dd,a.dep,a.rem from mf_ck a WITH(NOLOCK)
where a.ck_no=:ck_no
    """
#     # 查询需要推送的数据
#     sql_select = """
# select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_ck a
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
# group by c.u9cid,a.wh,a.bat_no,a.prd_no
# """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,cast(isnull(a.qty,0) as float)as qty ,a.bat_no,a.prd_no from tf_ck a WITH(NOLOCK)
left join tf_cktz_z b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_cktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.ck_no=:ck_no
"""

    # 更新推送时间
    sql_u = "update mf_ck_z set pull_id='T' where ck_no=:ck_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询出库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"ck_no": itm_bin['ck_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询出库入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['itemType'] = -1
        addData[0]['rcvDocTypeCode'] = "RCV21"
        addData[0]['srcDocType'] = 6
        addData[0]['DocStatus'] = 2
        addData[0]['Memo'] = data['rem']
        addData[0]['ConfirmDate'] = data['ck_dd']
        addData[0]['OtherID'] = data['ck_no']
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {
                # "SrcRcvLine":"1002411200000025",
                "SrcRcvLine":itm_1['u9cid'],
                "RejectQty": {
                    # "Amount1": 1,
                    "Amount1": itm_1['qty'],
                    "Amount2": 0
                },
                "RtnFillQty": {
                    # "Amount1":1,
                    "Amount1":itm_1['qty'],
                    "Amount2": 0
                },
                "RtnDeductQty": {
                    "Amount1": 0,
                    "Amount2": 0
                },
                "CooperateFromRcv": 0,
                "CooperateFromRcvLine": 0,
                "RtnWh": 0,
                "SrcID": 0,
                "InvLotCode":itm_1['bat_no'],
                # "InvLotCode":itm_1['bat_no'],
            }
            tf_data_list.append(itm_data)
        addData[0]['transformDTOs'] = tf_data_list

        logData['order'] = data["ck_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 调用u9capi生成采购退货单
        u9c_,err = u9cApiList.addPoTh(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '采购退货推送失败' + data["ck_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库库' + data["ck_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# 销货出库
def wmsapiPullU9cSaMfPSS(db, taskObj):
    print('wms销货出库')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    # userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return
    # 查询拆分
#     sql_select_bin = """
# select c.bil_no as tz_no from mf_ck a
# left join mf_ck_z b on a.ck_no=b.ck_no
# left join tf_ck c on a.ck_no=c.ck_no
# where isnull(b.pull_id,'')!='T'
# group by c.bil_no
#     """
    sql_select_bin = """
select a.ck_no from mf_ck a WITH(NOLOCK)
left join mf_ck_z b WITH(NOLOCK) on a.ck_no=b.ck_no 
left join tf_ck c WITH(NOLOCK) on a.ck_no=c.ck_no
where isnull(b.pull_id,'')!='T' and type_id='01' and isnull(a.erp_ap_no,'') != ''
    """
#     sql_select_mf = """
# select a.ck_no,a.itm,CONVERT(VARCHAR(100), a.ck_no, 23) as ck_no from tf_ck a
# left join mf_ck a1 on a.ck_no=b.ck_no
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
#     """

    sql_select_mf = """
select a.ck_no,CONVERT(VARCHAR(100), a.ck_dd, 23) as ck_dd,a.dep,a.rem,a.erp_ap_no,a.rem from mf_ck a WITH(NOLOCK)
where a.ck_no=:ck_no
    """
#     # 查询需要推送的数据
#     sql_select = """
# select c.u9cid,a.wh,sum(cast(isnull(a.qty,0) as float)) as qty ,a.bat_no,a.prd_no from tf_ck a
# left join tf_cktz_z b on a.bil_no=b.tz_no and a.bil_itm=b.itm
# left join tf_cktz_z c on c.tz_no=b.tz_no and c.itm=b.itm
# where a.bil_no=:tz_no
# group by c.u9cid,a.wh,a.bat_no,a.prd_no
# """
    # 查询需要推送的数据
    sql_select = """
select c.u9cid,a.wh,cast(isnull(a.qty,0) as float) as qty ,a.bat_no,a.prd_no,a.itm,a.rem,a.erp_ap_itm from tf_ck a WITH(NOLOCK)
left join tf_cktz b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_cktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.ck_no=:ck_no
"""

    # 更新推送时间
    sql_u = "update mf_ck_z set pull_id='T' where ck_no=:ck_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询出库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"ck_no": itm_bin['ck_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询出库入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = []
        for itm_1 in tf_data:
            itm_data = {
                "SrcDocType":2,
                "SrcDocNo":data['erp_ap_no'],
                "SrcDocLineNo":itm_1['erp_ap_itm'],
                "SrcDocSubLineNo":0,
                "wh":0,
                "WHCode":itm_1['wh'],
                "CurShipQty1":itm_1['qty'],
                "CurShipQty2":0,
                "SOIsConsign":True,
                "OptionalSplitTerm":[],
                "DescFlexField":{},
                "LineDescFlexField":{},
                "ShipDate":data['ck_dd'],
                "ShipConfirmDate":data['ck_dd'],
                "ShipMemo":data['rem'],
                "ShipLineMemo":itm_1['rem'],
                "ShipDocTypeID":0,
                "ShipDocTypeCode":'SH1',
                "DocNo":data['ck_no']
            }
            addData.append(itm_data)

        logData['order'] = data["ck_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新出库库' + data["ck_no"] + err
            add(db, logData)
            continue
        # 调用u9capi生成标准出库单
        u9c_,err = u9cApiList.addSaCk(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '销货出库推送失败' + data["ck_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return


# 销售退回收货单
def wmsapiPullU9cSbMfPss(db, taskObj):
    print('wms销售退回收货单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.rk_no,a.rk_no as code from mf_rk a WITH(NOLOCK)
left join mf_rk_z b WITH(NOLOCK) on a.rk_no=b.rk_no
where type_id='03' and isnull(b.pull_id,'')!='T'
    """


    sql_select_mf = """
select a.rk_no,CONVERT(VARCHAR(100), a.rk_dd, 23) as rk_dd,a.dep,a.rem from mf_rk a WITH(NOLOCK)
where a.rk_no=:rk_no
    """

    # 查询需要推送的数据
    sql_select = """
select b.org_bil_no,c.u9cid,a.wh,cast(isnull(a.qty,0) as float) as qty ,a.bat_no,a.prd_no from tf_rk a WITH(NOLOCK)
left join tf_rktz b WITH(NOLOCK) on a.bil_no=b.tz_no and a.bil_itm=b.itm 
left join tf_rktz_z c WITH(NOLOCK) on c.tz_no=b.tz_no and c.itm=b.itm
where a.rk_no=:rk_no
"""

    # 更新推送时间
    sql_u = "update mf_rk_z set pull_id='T' where rk_no=:rk_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"rk_no": itm_bin['rk_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询入库表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['DocStatus'] = 0
        addData[0]['OtherID'] = data["rk_no"]
        addData[0]['sMToRcvDTOs'] = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['BusinessDate'] = data["rk_dd"]
            itm_data['SrcDocType'] = 8
            itm_data['TransQty'] = {
                "Amount1":itm_1['qty'],
                "Amount2":0
            }
            itm_data['SrcLineID'] = itm_1['u9cid']
            itm_data['SrcDocNo'] = itm_1['org_bil_no']
            addData[0]['sMToRcvDTOs'].append(itm_data)

        logData['order'] = data["rk_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 调用u9capi生成材料出库单
        u9c_,err = u9cApiList.addSaTh(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '入库单推送失败' + data["rk_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新入库库' + data["rk_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# 审核u9c标准收货单行
def wmsapiPullU9cShBiaoZhunShouHuoRow(db, taskObj):
    print('wms审核标准收货单行')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    # 获取org组织编码
    org = u9cApiList.orgCode
    # 查询需要推送的数据
    sql_select = """
select c.u9cid, a.type_id,b.org_bil_itm,b.itm,b.erp_bil_no,a.cls_id,a.tz_no from mf_rktz a WITH(NOLOCK)
left join tf_rktz b WITH(NOLOCK) on a.tz_no=b.tz_no
left join tf_rktz_z c WITH(NOLOCK) on b.tz_no=c.tz_no and b.itm=c.itm
where isnull(c.u9chsh,'')!='T' and a.type_id='01' and isnull(c.u9cid,'')!='' and 
(isnull(b.qty_rk,0)>=isnull(b.qty,0) or (isnull(a.cls_id,'')='T' and isnull(b.qty_rk,0)<isnull(b.qty,0)))
"""

    # 更新推送时间
    sql_u = "update tf_rktz_z set u9chsh='T' where tz_no=:tz_no and itm=:itm"

    # 查询需要审核的行
    tf_data, err = dbListJon(sql=sql_select, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
        add(db, logData)
        return

    for itm in tf_data:
        sh_data = [
            {
                "RcvLineID": itm['u9cid'],
                "OtherID": "",
                "RcvDocNo": itm['erp_bil_no'],
                "DocLineNo": itm['org_bil_itm'],
                "OrgCode": org
            }
        ]
        logData['order'] = itm["tz_no"]
        logData['data'] = json.dumps(sh_data, ensure_ascii=False)
        # 提交审核并u9c标准收货单行失败
        u9c_, err = u9cApiList.bzshRowSh(sh_data)
        if err != None:
            logData['status'] = False
            logData['rem'] = '提交审核并u9c标准收货单行失败' + itm["tz_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新审核标识' + itm["tz_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# 审核成品入库单
def wmsapiPullU9cMmSh(db, taskObj):
    print('审核u9c杂收单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.oth_bil_no as code,a.tz_no from mf_rktz a WITH(NOLOCK)
left join mf_rktz_z b WITH(NOLOCK) on a.tz_no=b.tz_no
left join (select sum(isnull(qty,0)) as qty,sum(isnull(qty_rk,0)) as qty_rk,tz_no from 
tf_rktz WITH(NOLOCK) group by tz_no) c on a.tz_no=c.tz_no
where 
isnull(c.tz_no,'')!='' and  c.qty_rk>=c.qty
and type_id='04' and isnull(b.pull_id,'')!='T'
    """
    # 更新推送时间
    sql_u = "update mf_rktz_z set pull_id='T' where tz_no=:tz_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库通知单' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:
        logData['order'] = itm_bin["tz_no"]
        logData['data'] = json.dumps(itm_bin, ensure_ascii=False)
        # 调用u9capi审核成品入库单
        u9c_,err = u9cApiList.chengPingShenHe(itm_bin['code'])
        if err != None:
            logData['status'] = False
            logData['rem'] = '审核u9c成品入库单失败' + itm_bin["code"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新入库通知推送标识' + itm_bin["tz_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

 #审核调入单（调拨单）
def wmsapiPullU9cCkDb(db, taskObj):
    print('审核u9c调入单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.erp_bil_no as code from mf_ictz a WITH(NOLOCK)
left join mf_ictz_z b WITH(NOLOCK) on a.tz_no=b.tz_no
left join (select sum(isnull(qty,0)) as qty,sum(isnull(qty_ck,0)) as qty_ck,erp_bil_no from 
tf_ictz WITH(NOLOCK) group by erp_bil_no) c on a.erp_bil_no=c.erp_bil_no
where 
isnull(c.erp_bil_no,'')!='' and  c.qty_ck>=c.qty
and type_id in('17','07') and isnull(b.pull_id,'')!='T'
group by a.erp_bil_no
    """
    # 更新推送时间
    sql_u = "update a set a.pull_id='T' from mf_ictz_z a,mf_ictz b where a.tz_no=b.tz_no and b.erp_bil_no=:code"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询调拨通知单' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:
        logData['order'] = itm_bin["code"]
        logData['data'] = json.dumps(itm_bin, ensure_ascii=False)
        # 调用u9capi审核成品入库单
        u9c_,err = u9cApiList.diaoRUShenHe(itm_bin['code'])
        if err != None:
            logData['status'] = False
            logData['rem'] = '审核u9c调入单失败' + itm_bin["code"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新调拨通知推送标识' + itm_bin["code"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# 托工入库审核
def wmsapiPullU9cTwMmSh(db, taskObj):
    print('审核u9c托工入库单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.oth_bil_no as code from mf_rktz a WITH(NOLOCK)
left join mf_rktz_z b WITH(NOLOCK) on a.tz_no=b.tz_no
left join (select sum(isnull(qty,0)) as qty,sum(isnull(qty_rk,0)) as qty_rk,oth_bil_no from 
tf_rktz WITH(NOLOCK) group by oth_bil_no) c on a.oth_bil_no=c.oth_bil_no
where 
isnull(c.oth_bil_no,'')!='' and  c.qty_rk>=c.qty
and type_id='05' and isnull(b.pull_id,'')!='T'
group by a.oth_bil_no
    """
    # 更新推送时间
    sql_u = "update a set a.pull_id='T' from mf_rktz_z a,mf_rktz b where a.tz_no=b.tz_no and b.oth_bil_no=:code"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询入库通知单' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:
        logData['order'] = itm_bin["code"]
        logData['data'] = json.dumps(itm_bin, ensure_ascii=False)
        # 调用u9capi审核杂收单
        u9c_, err = u9cApiList.tWRkSh(itm_bin['code'])
        if err != None:
            logData['status'] = False
            logData['rem'] = '审核u9c杂收单失败' + itm_bin["code"] + err
            add(db, logData)
            continue
        # 更新推送标识
        _, err = dbEdit(sql=sql_u, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新入库通知推送标识' + itm_bin["code"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return

# wms推送盘盈盘亏单
def wmsapiPullU9cPanYingKui(db, taskObj):
    print('wms推送盘盈盘亏单')
    logData = {}
    logData['status'] = True
    logData['name'] = taskObj["name"]
    logData['order'] = ""
    logData['data'] = ""
    logData['rem'] = ""

    # 获取token
    token, err = wmsApiList.getToken()
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    # 获取拉取连接
    db_config = openDbBase()
    db_push_session = get_yun_session(engineName=None, dbpool=db_config['u9capi'])
    userCode = u9cApiList.userCode
    # 获取org组织编码
    org, err = u9cApiList.orgCodeConfig(db_push_session)
    if err != None:
        logData['status'] = False
        logData['rem'] = err
        add(db, logData)
        return

    sql_select_bin = """
select a.ku_no as p_no,b.pull_id  from mf_ku a
inner join mf_ku_z b on a.ku_no=b.ku_no
where isnull(b.pull_id,'') !='T'
union all
select a.yn_no as p_no,b.pull_id from mf_yn a 
inner join mf_yn_z b on a.yn_no=b.yn_no
where isnull(b.pull_id,'') !='T'
    """


    sql_select_mf = """
select a.ku_no as p_no,b.pull_id,CONVERT(VARCHAR(100), a.ku_dd, 23) as p_dd,a.dep,a.wh,'K' as p_type,a.rem  from mf_ku a
inner join mf_ku_z b on a.ku_no=b.ku_no
where isnull(b.pull_id,'') !='T' and a.ku_no=:p_no
union all
select a.yn_no as p_no,b.pull_id,CONVERT(VARCHAR(100), a.yn_dd, 23) as p_dd,a.dep,a.wh,'Y' as p_type,a.rem from mf_yn a 
inner join mf_yn_z b on a.yn_no=b.yn_no
where isnull(b.pull_id,'') !='T'  and a.yn_no=:p_no
    """

    # 查询需要推送的数据
    sql_select = """
select a.* from (
select prd_no,cast(qty as float) as qty,bat_no,wh,ku_no as p_no from tf_ku
union all
select prd_no,cast(qty as float) as qty,bat_no,wh,yn_no as p_no from tf_yn
) a
where a.p_no=:p_no
"""

    # 更新推送时间
    sql_u1 = "update mf_ku_z set pull_id='T' where ku_no=:p_no"
    sql_u2 = "update mf_yn_z set pull_id='T' where yn_no=:p_no"

    dataBin, err = dbListJon(sql=sql_select_bin, engineName=None, parm={}, db_session=db)
    if err != None:
        logData['status'] = False
        logData['rem'] = '查询盘盈亏单汇总' + sql_select_bin + err
        add(db, logData)
        return
    for itm_bin in dataBin:

        # 1、查询需要推送的数据
        data, err = dbJonOne(sql=sql_select_mf, engineName=None, parm={"p_no": itm_bin['p_no']}, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询盘盈亏单表头' + sql_select + err
            add(db, logData)
            return
        # 查询表身需要推送的数据
        tf_data, err = dbListJon(sql=sql_select, engineName=None, parm=itm_bin, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '查询表身推送数据列表异常' + sql_select + err
            add(db, logData)
            return
        # 拼接数据
        addData = [{}]
        addData[0]['OtherID'] = itm_bin['p_no']
        addData[0]['DepartmentCode'] = data['dep']
        addData[0]['GLCloseDate'] = data['p_dd']
        addData[0]['IsGLClose'] = False
        addData[0]['Status'] = 0
        addData[0]['DocumentType_Code'] = "PDCYD"
        addData[0]['BusinessDate'] = data['p_dd']
        addData[0]['Memo'] = data['rem']
        tf_data_list = []
        for itm_1 in tf_data:
            itm_data = {}
            itm_data['ItemInfo'] = {
                "ItemID":0,
                "ItemCode":itm_1['prd_no'],
                "ItemName":"",
                "ItemGrade":0,
                "ItemPotency":0,
            }
            itm_data['OwnerOrg_Code'] = ""
            itm_data['StorageType'] = 0
            itm_data['Wh_Code'] = itm_1['wh']
            itm_data['Org_Code'] = org
            if data['p_type'] == 'Y':
                # 盘盈
                itm_data['AdjustType'] = 0
                itm_data['PlusQtySU'] = itm_1['qty']
                itm_data['MinusQtySU'] = 0
            else:
                # 盘亏
                itm_data['AdjustType'] = 1
                itm_data['PlusQtySU'] = 0
                itm_data['MinusQtySU'] = itm_1['qty']
            itm_data['AdjQtySU'] = itm_1['qty']
            itm_data['LotCode'] = itm_1['bat_no']
            itm_data['PlusQtyCU'] = 0
            itm_data['AdjQtyCU'] = 0
            itm_data['MinusQtyCU'] = 0
            tf_data_list.append(itm_data)
        addData[0]['CheckDiffBilLineRData'] = tf_data_list
        logData['order'] = data["p_no"]
        logData['data'] = json.dumps(addData, ensure_ascii=False)
        # 调用u9capi生成盘盈亏单
        u9c_,err = u9cApiList.addPYingKui(addData)
        if err != None:
            logData['status'] = False
            logData['rem'] = '盘盈亏单推送失败' + data["p_no"] + err
            add(db, logData)
            continue
        # 更新推送标识
        print('更新单据')
        if data['p_type'] == 'Y':
            sql_u = sql_u2
        else:
            sql_u = sql_u1
        _, err = dbEdit(sql=sql_u, engineName=None, parm=data, db_session=db)
        if err != None:
            logData['status'] = False
            logData['rem'] = '更新单据' + data["p_no"] + err
            add(db, logData)
            continue
        logData['status'] = True
        logData['rem'] = json.dumps(u9c_, ensure_ascii=False)
        add(db, logData)
    return
